"""
数据源相关的API路由
"""

from fastapi import APIRouter, Depends, HTTPException, status, UploadFile, File, Query
from sqlalchemy.orm import Session
from typing import Optional, List

from database import get_db
from schemas.data_source import (
    DataSourceCreate, DataSourceUpdate, DataSourceResponse, DataSourceList,
    ConnectionTestRequest, ConnectionTestResponse, DataPreviewRequest, DataPreviewResponse,
    DataSourceType, DataSourceTablesResponse
)
from services.data_source_service import DataSourceService
from utils.dependencies import get_current_active_user
from models.user import User

router = APIRouter()


@router.post("/", response_model=DataSourceResponse, summary="创建数据源")
async def create_data_source(
    data_source_data: DataSourceCreate,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """
    创建新的数据源
    
    - **name**: 数据源名称
    - **type**: 数据源类型（mysql/csv/excel）
    - **config**: 数据源配置信息
    - **description**: 数据源描述（可选）
    """
    data_source = DataSourceService.create_data_source(db, data_source_data, current_user.id)
    return DataSourceResponse.from_orm(data_source)


@router.get("/", response_model=DataSourceList, summary="获取数据源列表")
async def get_data_sources(
    page: int = Query(1, ge=1, description="页码"),
    size: int = Query(10, ge=1, le=100, description="每页数量"),
    search: Optional[str] = Query(None, description="搜索关键词"),
    type: Optional[DataSourceType] = Query(None, description="数据源类型过滤"),
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """
    获取数据源列表，支持分页、搜索和类型过滤
    
    - **page**: 页码（从1开始）
    - **size**: 每页数量（1-100）
    - **search**: 搜索关键词（可选）
    - **type**: 数据源类型过滤（可选）
    """
    return DataSourceService.get_data_sources(db, current_user.id, page, size, search, type)


# 将具体的路由放在前面，避免被通用路由拦截
@router.get("/{data_source_id}/tables", response_model=DataSourceTablesResponse, summary="获取数据源表列表")
async def get_data_source_tables(
    data_source_id: int,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """
    获取数据源中的表列表

    - **data_source_id**: 数据源ID
    """
    return DataSourceService.get_data_source_tables(db, data_source_id, current_user.id)


@router.get("/{data_source_id}/preview", response_model=DataPreviewResponse, summary="预览指定数据源数据")
async def preview_data_source(
    data_source_id: int,
    table_name: str = Query(None, description="表名"),
    limit: int = Query(10, ge=1, le=100),
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """
    预览指定数据源中的数据

    - **data_source_id**: 数据源ID
    - **table_name**: 表名（可选，不指定则使用第一个表）
    - **limit**: 预览行数（1-100）
    """
    preview_request = DataPreviewRequest(data_source_id=data_source_id, limit=limit, table_name=table_name)
    return DataSourceService.preview_data(db, preview_request, current_user.id)


@router.get("/{data_source_id}", response_model=DataSourceResponse, summary="获取数据源详情")
async def get_data_source(
    data_source_id: int,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """
    获取指定数据源的详细信息

    - **data_source_id**: 数据源ID
    """
    data_source = DataSourceService.get_data_source(db, data_source_id, current_user.id)
    if not data_source:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="数据源不存在"
        )
    return DataSourceResponse.from_orm(data_source)


@router.put("/{data_source_id}", response_model=DataSourceResponse, summary="更新数据源")
async def update_data_source(
    data_source_id: int,
    data_source_data: DataSourceUpdate,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """
    更新指定数据源的信息
    
    - **data_source_id**: 数据源ID
    - **name**: 数据源名称（可选）
    - **config**: 数据源配置信息（可选）
    - **description**: 数据源描述（可选）
    """
    data_source = DataSourceService.update_data_source(db, data_source_id, data_source_data, current_user.id)
    if not data_source:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="数据源不存在"
        )
    return DataSourceResponse.from_orm(data_source)


@router.delete("/{data_source_id}", summary="删除数据源")
async def delete_data_source(
    data_source_id: int,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """
    删除指定的数据源
    
    - **data_source_id**: 数据源ID
    """
    success = DataSourceService.delete_data_source(db, data_source_id, current_user.id)
    if not success:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail="数据源不存在"
        )
    return {"message": "数据源删除成功"}


@router.post("/test-connection", response_model=ConnectionTestResponse, summary="测试数据源连接")
async def test_connection(
    test_request: ConnectionTestRequest,
    current_user: User = Depends(get_current_active_user)
):
    """
    测试数据源连接是否正常
    
    - **type**: 数据源类型
    - **config**: 数据源配置信息
    """
    return DataSourceService.test_connection(test_request)


@router.post("/preview", response_model=DataPreviewResponse, summary="预览数据源数据")
async def preview_data(
    preview_request: DataPreviewRequest,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """
    预览数据源中的数据

    - **data_source_id**: 数据源ID
    - **limit**: 预览行数（1-100）
    """
    return DataSourceService.preview_data(db, preview_request, current_user.id)





@router.post("/upload", summary="上传文件")
async def upload_file(
    file: UploadFile = File(...),
    current_user: User = Depends(get_current_active_user)
):
    """
    上传CSV或Excel文件
    
    - **file**: 要上传的文件（支持.csv, .xlsx, .xls格式）
    """
    file_path = DataSourceService.upload_file(file, current_user.id)
    return {
        "message": "文件上传成功",
        "file_path": file_path,
        "filename": file.filename
    }


@router.get("/excel/{file_path:path}/sheets", summary="获取Excel工作表")
async def get_excel_sheets(
    file_path: str,
    current_user: User = Depends(get_current_active_user)
):
    """
    获取Excel文件的所有工作表名称
    
    - **file_path**: Excel文件路径
    """
    sheets = DataSourceService.get_excel_sheets(file_path)
    return {"sheets": sheets}


@router.get("/{data_source_id}/tables/{table_name}/fields", summary="获取表字段")
async def get_table_fields(
    data_source_id: int,
    table_name: str,
    identify_types: bool = Query(False, description="是否识别字段类型"),
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """获取指定表的字段信息"""
    try:
        fields = DataSourceService.get_table_fields(
            db, data_source_id, table_name, current_user.id, identify_types
        )
        return fields
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"获取表字段失败: {str(e)}")


@router.get("/{data_source_id}/tables/{table_name}/data", summary="获取表数据")
async def get_table_data(
    data_source_id: int,
    table_name: str,
    fields: str = Query(..., description="要获取的字段，逗号分隔"),
    limit: int = Query(100, description="限制返回行数"),
    conditions: str = Query("", description="筛选条件JSON"),
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """获取表数据"""
    try:
        data = DataSourceService.get_table_data(
            db, data_source_id, table_name, fields, limit, conditions, current_user.id
        )
        return {"data": data}
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"获取表数据失败: {str(e)}")


@router.get("/statistics", summary="获取数据源统计")
async def get_statistics(
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_active_user)
):
    """
    获取当前用户的数据源统计信息
    """
    stats = DataSourceService.get_data_source_statistics(db, current_user.id)
    return stats
